const mysql = require("mysql");

// 连接信息
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "12345678",
  database: "students",
  timezone: "08:00",
});

// 建立连接
/// connection.connect();
connection.connect(function (err) {
  if (err) {
    console.error("error connecting: " + err.stack);
    return;
  }
  console.log("connected as id " + connection.threadId);
});
/* 插入数据方法
var data = { id: 2, name: 'jim', age:18, gender:"M", inclass: 2, grade: 9,submission_data: '2021-08-31' };
connection.query('insert into student set ?', data,
function (error, results, fields) {
    if (error) {
        throw error;
    }

    // 打印查询结果
    console.log('INSERT result is: ', results);
}); 
*/

// 或者
/* 
connection.query("insert into student (id,name,age,gender,inclass,grade) values (1000010,'joys',15,'M',2,3),(1000021,'sams',16,'M',3,3);",
function (error, results, fields) {
    if (error) {
        throw error;
    }

    // 打印查询结果
    console.log('INSERT result is: ', results);
}); 
*/

//更新属性,把name=sams的同学修改age为12
connection.query(
  "update student set age = ? where name = ?",
  [12, "sams"],
  function (error, results, fields) {
    if (error) {
      throw error;
    }

    // 打印查询结果
    console.log("UPDATE result is: ", results);
  }
);
// 删除数据
connection.query(
  "delete from student where id > 1000000",
  function (error, results, fields) {
    if (error) {
      throw error;
    }

    // 打印查询结果
    console.log("DELETE result is: ", results);
  }
);

connection.query(
  "SELECT * from student where age > 12",
  function (error, results, fields) {
    if (error) throw error;
    console.log("查询年龄大于12: ", JSON.parse(JSON.stringify(results)));
  }
);

connection.end(function (err) {
  if (err) {
    console.error("error end: " + err.stack);
    return;
  }
  console.log("end connection");
});
